package com.cloud.utils;

import com.cloud.entity.vo.FieldParam;

import java.util.List;

/**
 * 达梦数据库创建Sql工具
 *
 * @author yzj
 */
public class SQLCreateUtils {

    /**
     * 创建MySql表语句
     *
     * @param tableName      表名称
     * @param tableComment   表说明
     * @param fieldParamList 表字段
     * @return
     */
    public static String addMySqlTable(String tableName, String tableComment, List<FieldParam> fieldParamList) {
        StringBuilder sql = new StringBuilder();
        StringBuffer fieldKey = new StringBuffer();
        StringBuffer fieldIndex = new StringBuffer();
        sql.append("CREATE TABLE `").append(tableName).append("` (");
        // 表Sql字段拼接
        for (int i = 0; i < fieldParamList.size(); i++) {
            FieldParam fieldParam = fieldParamList.get(i);
            sql.append("`").append(fieldParam.getColumnName()).append("` ").append(fieldParam.getColumnType());
            // 判断是否必填
            if (StringUtils.isNotEmpty(fieldParam.getIsRequired())) {
                if (fieldParam.getIsRequired().equals("1")) {
                    sql.append(" NOT NULL");
                } else {
                    sql.append(" NULL DEFAULT NULL");
                }
            }
            // 判断是否主键自增
            if (StringUtils.isNotEmpty(fieldParam.getIsIncrement())) {
                if (fieldParam.getIsIncrement().equals("1")) {
                    sql.append(" AUTO_INCREMENT");
                }
            }
            // 设置字段注释
            if (StringUtils.isNotEmpty(fieldParam.getColumnComment())) {
                // 最后一个逗号不拼接
                if (i == (fieldParamList.size() - 1)) {
                    sql.append(" COMMENT '").append(fieldParam.getColumnComment()).append("'");
                } else {
                    sql.append(" COMMENT '").append(fieldParam.getColumnComment()).append("',");
                }
            }
            // 字段是否主键
            if (StringUtils.isNotEmpty(fieldParam.getIsKey())) {
                if (fieldParam.getIsKey().equals("1")) {
                    fieldKey.append("`").append(fieldParam.getColumnName()).append("`,");
                }
            }
            // 是否索引字段
            if (StringUtils.isNotEmpty(fieldParam.getIsIndex())) {
                if (fieldParam.getIsIndex().equals("1")) {
                    fieldIndex.append("`").append(fieldParam.getColumnName()).append("`,");
                }
            }
        }
        // Sql拼接主键
        if (StringUtils.isNotEmpty(fieldKey)) {
            sql.append(", PRIMARY KEY (").append(fieldKey.deleteCharAt(fieldKey.length() - 1)).append(") USING BTREE ");
        }
        // Sql拼接索引
        if (StringUtils.isNotEmpty(fieldIndex)) {
            sql.append(", INDEX (").append(fieldIndex.deleteCharAt(fieldIndex.length() - 1)).append(")");
        }
        sql.append(") ENGINE = INNODB DEFAULT CHARACTER SET = utf8 COMMENT = '")
                .append(tableComment).append("' ROW_FORMAT = Dynamic;");
        return sql.toString();
    }

    /**
     * 编辑MySql表字段
     *
     * @param tableName         表名称
     * @param oldFieldParamList 旧表字段
     * @param newFieldParamList 新表字段
     * @return
     */
    public static String editMySqlTable(String tableName, List<FieldParam> oldFieldParamList,
                                        List<FieldParam> newFieldParamList) {
        StringBuilder delSql = new StringBuilder();
        // 删除旧字段
        delSql.append("ALTER TABLE `").append(tableName).append("`");
        for (int i = 0; i < oldFieldParamList.size(); i++) {
            FieldParam oldFieldParam = oldFieldParamList.get(i);
            if (i == (oldFieldParamList.size() - 1)) {
                delSql.append(" DROP `").append(oldFieldParam.getColumnName()).append("`;");
            } else {
                delSql.append(" DROP `").append(oldFieldParam.getColumnName()).append("`, ");
            }
        }
        // 添加新增段
        StringBuilder saveSql = new StringBuilder();
        saveSql.append("ALTER TABLE `").append(tableName).append("`");
        for (int i = 0; i < newFieldParamList.size(); i++) {
            FieldParam newFieldParam = newFieldParamList.get(i);
            saveSql.append(" ADD COLUMN `").append(newFieldParam.getColumnName()).append("` ")
                    .append(newFieldParam.getColumnType());
            // 判断是否必填
            if (StringUtils.isNotEmpty(newFieldParam.getIsRequired())) {
                if (newFieldParam.getIsRequired().equals("1")) {
                    saveSql.append(" NOT NULL");
                } else {
                    saveSql.append(" NULL DEFAULT NULL");
                }
            }
            // 设置字段注释
            if (i == (newFieldParamList.size() - 1)) {
                saveSql.append(" COMMENT '").append(newFieldParam.getColumnComment()).append("'");
            } else {
                saveSql.append(" COMMENT '").append(newFieldParam.getColumnComment()).append("',");
            }
        }
        saveSql.append("; ");
        return delSql.toString() + saveSql.toString();
    }

    /**
     * 编辑Mysql表名称
     *
     * @param tableName
     * @return
     */
    public static String editMysqlTableName(String tableName) {
        String dataTime = ToolUtils.getDataNameFormat();
        StringBuilder sql = new StringBuilder();
        sql.append("RENAME TABLE ").append(tableName).append(" TO ").append("delete_")
                .append(tableName).append("_" + dataTime);
        return sql.toString();
    }

    /**
     * 创建DM数据表SQL
     *
     * @param dbName         数据库名称
     * @param tableName      表名称
     * @param tableComment   表说明
     * @param fieldParamList 字段列表
     * @return
     */
    public static String addDMTable(String dbName, String tableName, String tableComment, List<FieldParam> fieldParamList) {
        StringBuilder sql = new StringBuilder();
        StringBuffer fieldKey = new StringBuffer();
        StringBuffer fieldNotes = new StringBuffer();
        sql.append("CREATE TABLE ").append("\"").append(dbName).append("\"").append(".")
                .append("\"").append(tableName).append("\"").append(" (");
        // 设置数据库表说明
        fieldNotes.append("COMMENT ON TABLE ").append("\"").append(dbName).append("\"").append(".")
                .append("\"").append(tableName).append("\"").append(" IS '").append(tableComment).append("';");
        // 表Sql字段拼接
        for (int i = 0; i < fieldParamList.size(); i++) {
            FieldParam fieldParam = fieldParamList.get(i);
            sql.append("\"").append(fieldParam.getColumnName()).append("\"");
            // 判断是否主键自增
            if (StringUtils.isNotEmpty(fieldParam.getIsIncrement())) {
                if (fieldParam.getIsIncrement().equals("1")) {
                    sql.append(" INT IDENTITY(1, 1)");
                } else {
                    sql.append(" ").append(fieldParam.getColumnType());
                }
            }
            // 判断是否必填
            if (StringUtils.isNotEmpty(fieldParam.getIsRequired())) {
                if (fieldParam.getIsRequired().equals("1")) {
                    if (i == (fieldParamList.size() - 1)) {
                        sql.append(" NOT NULL");
                    } else {
                        sql.append(" NOT NULL").append(",");
                    }
                } else {
                    if (i != (fieldParamList.size() - 1)) {
                        sql.append(",");
                    }
                }
            }
            // 字段是否主键
            if (StringUtils.isNotEmpty(fieldParam.getIsKey())) {
                if (fieldParam.getIsKey().equals("1")) {
                    fieldKey.append("\"").append(fieldParam.getColumnName()).append("\"").append(",");
                }
            }
            // 设置表字段说明
            if (StringUtils.isNotEmpty(fieldParam.getColumnComment())) {
                fieldNotes.append("COMMENT ON COLUMN ").append("\"").append(dbName).append("\"")
                        .append(".").append("\"").append(tableName).append("\"").append(".")
                        .append("\"").append(fieldParam.getColumnName()).append("\"").append(" IS '")
                        .append(fieldParam.getColumnComment()).append("';");
            }
        }
        // Sql拼接主键
        if (StringUtils.isNotEmpty(fieldKey)) {
            sql.append(", UNIQUE (").append(fieldKey.deleteCharAt(fieldKey.length() - 1)).append(")");
        }
        sql.append(") STORAGE (ON ").append("\"").append("MAIN").append("\"").append(", ").append("CLUSTERBTR);");
        return sql.toString() + fieldNotes.toString();
    }

    /**
     * 编辑DM数据表SQL
     *
     * @param dbName            数据库名称
     * @param tableName         表名称
     * @param oldFieldParamList 旧字段列表
     * @param newFieldParamList 新字段列表
     * @return
     */
    public static String editDMTable(String dbName, String tableName, List<FieldParam> oldFieldParamList,
                                     List<FieldParam> newFieldParamList) {
        StringBuilder delField = new StringBuilder();
        for (int i = 0; i < oldFieldParamList.size(); i++) {
            FieldParam oldFieldParam = oldFieldParamList.get(i);
            // 删除数据库表旧字段
            delField.append("ALTER TABLE ").append("\"").append(dbName).append("\"").append(".").append("\"")
                    .append(tableName).append("\"");
            delField.append(" DROP COLUMN ").append("\"").append(oldFieldParam.getColumnName()).append("\"").append(";");
        }
        // 添加新增段
        StringBuilder saveField = new StringBuilder();
        StringBuffer fieldNotes = new StringBuffer();
        for (int i = 0; i < newFieldParamList.size(); i++) {
            FieldParam newFieldParam = newFieldParamList.get(i);
            saveField.append("ALTER TABLE ").append("\"").append(dbName).append("\"").append(".")
                    .append("\"").append(tableName).append("\"");
            saveField.append(" ADD COLUMN ").append("\"").append(newFieldParam.getColumnName())
                    .append("\"").append(" ").append(newFieldParam.getColumnType());
            // 判断是否必填
            if (StringUtils.isNotEmpty(newFieldParam.getIsRequired())) {
                if (newFieldParam.getIsRequired().equals("1")) {
                    saveField.append(" NOT NULL");
                }
            }
            saveField.append(";");
            // 设置表字段说明
            if (StringUtils.isNotEmpty(newFieldParam.getColumnComment())) {
                fieldNotes.append("COMMENT ON COLUMN ").append("\"").append(dbName).append("\"")
                        .append(".").append("\"").append(tableName).append("\"").append(".")
                        .append("\"").append(newFieldParam.getColumnName()).append("\"").append(" IS '")
                        .append(newFieldParam.getColumnComment()).append("';");
            }
        }
        return delField.toString() + saveField.toString() + fieldNotes.toString();
    }

    /**
     * 编辑DM 表名称
     *
     * @param dbName    数据库名称
     * @param tableName 表名称
     * @return
     */
    public static String editDMTableName(String dbName, String tableName) {
        String dataTime = ToolUtils.getDataNameFormat();
        StringBuilder sql = new StringBuilder();
        sql.append("ALTER TABLE ").append("\"").append(dbName).append("\"").append(".").append("\"")
                .append(tableName).append("\"")
                .append(" RENAME TO ").append("\"").append("delete_").append(tableName)
                .append("_" + dataTime).append("\"");
        return sql.toString();
    }

}
